/*
 * 
 */
package com.spartancoder.accommodation.models;

import com.spartancoder.accommodation.beans.PlaceBean;
import com.spartancoder.accommodation.db.Database;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Logger;

/**
 *
 * @author andrei
 */
public class Place {

	private static Logger log = Logger.getLogger(Place.class.getName());
	private static Database db = Database.getInstance();

	public static void create(PlaceBean pb) {
		try {
			Connection conn = db.getConnection();
			String query = "insert into `places`(`name`,`address`, `rooms`, " +
					"`places_per_room`, `available`)values(?, ?, ?, ?, ?)";

			PreparedStatement st = conn.prepareStatement(query);
			st.setString(1, pb.getName());
			st.setString(2, pb.getAddress());
			st.setInt(3, pb.getRooms());
			st.setInt(4, pb.getPlacesPerRoom());
			st.setBoolean(5, pb.isAvailable());

			st.execute();

		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}

	public static void update(PlaceBean pb) {
		try {
			Connection conn = db.getConnection();
			String query = "update `places` set `name`=?, `address`=?, " +
					"`rooms`=?, `places_per_room`=?, `available`=? " +
					"where `place_id`=?";

			PreparedStatement st = conn.prepareStatement(query);
			st.setString(1, pb.getName());
			st.setString(2, pb.getAddress());
			st.setInt(3, pb.getRooms());
			st.setInt(4, pb.getPlacesPerRoom());
			st.setBoolean(5, pb.isAvailable());
			st.setInt(6, pb.getPlaceId());

			st.executeUpdate();

		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}

	public static boolean isLinked(int pId, int cId) {
		try {
			Connection conn = db.getConnection();
			String query = "select count(*) from `colleges_places` " +
					" where `college_id`=? and `place_id`=?";

			PreparedStatement st = conn.prepareStatement(query);
			st.setInt(1, cId);
			st.setInt(2, pId);

			ResultSet rs = st.executeQuery();
			if (rs.next()) {
				if(rs.getInt(1)==1) {
					return true;
				}
			}
			return false;
		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}

	public static void linkToCollege(int pId, int cId) {
		try {
			if( isLinked(pId, cId) ) {
				return;
			}
			
			Connection conn = db.getConnection();
			String query = "insert into `colleges_places`" +
					"(`college_id`, `place_id`)values(?,?)";

			PreparedStatement st = conn.prepareStatement(query);
			st.setInt(1, cId);
			st.setInt(2, pId);

			st.execute();

		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}

	public static void remove( int pId ) {
		try {
			Connection conn = db.getConnection();
			String query = "delete from `places` where `place_id`=?";

			PreparedStatement st = conn.prepareStatement(query);
			st.setInt(1, pId);

			st.execute();

		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}

	public static PlaceBean fetch( int pId ) {
		try {
			Connection conn = db.getConnection();
			String query = "select * from `places` where `place_id`=?";

			PreparedStatement st = conn.prepareStatement(query);
			st.setInt(1, pId);

			ResultSet rs = st.executeQuery();
			if(rs.next()) {
				PlaceBean pb = new PlaceBean();
				pb.fill(rs);
				return pb;
			}

			return null;
		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}

	public static List<PlaceBean> fetchByCollege( int cId ) {
		try {
			Connection conn = db.getConnection();
			String query = "select * from `places` p, `colleges_places` cp " +
					"where p.place_id = cp.place_id and cp.college_id = ?";
			PreparedStatement st = conn.prepareStatement(query);
			st.setInt(1, cId);

			ResultSet rs = st.executeQuery();
			List<PlaceBean> ret = new LinkedList<PlaceBean>();
			while(rs.next()) {
				PlaceBean pb = new PlaceBean();
				pb.fill(rs);
				ret.add(pb);
			}

			return ret;
		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}

	public static List<PlaceBean> fetchByStudent( int sId ) {
		try {
			Connection conn = db.getConnection();
			String query = "select p.* from `places` p, `options` op " +
				"where p.place_id = op.place_id and op.student_id = ? " +
				" and op.session_id = (" +
				"select session_id from `accommodation_sessions` " +
				"order by `start_date` desc limit 1)";
			PreparedStatement st = conn.prepareStatement(query);
			st.setInt(1, sId);

			ResultSet rs = st.executeQuery();
			List<PlaceBean> ret = new LinkedList<PlaceBean>();
			while(rs.next()) {
				PlaceBean pb = new PlaceBean();
				pb.fill(rs);
				ret.add(pb);
			}

			return ret;
		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}

	public static List<PlaceBean> fetchAll() {
		try {
			Connection conn = db.getConnection();
			String query = "select * from `places`";
			PreparedStatement st = conn.prepareStatement(query);

			ResultSet rs = st.executeQuery();
			List<PlaceBean> ret = new LinkedList<PlaceBean>();
			while(rs.next()) {
				PlaceBean pb = new PlaceBean();
				pb.fill(rs);
				ret.add(pb);
			}

			return ret;
		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}
}
